package com.gugee.ins.common.dal.mapper.statistics;

import com.gugee.common.mapper.GugeeMapper;
import com.gugee.ins.common.model.mysql.statistics.InsStBloggerFlBrandCategory;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * InsStBloggerFlBrandCategoryMapper
 * @author Huangxin
 */
public interface InsStBloggerFlBrandCategoryMapper extends GugeeMapper<InsStBloggerFlBrandCategory> {

    /**
     * 删除活跃粉丝品牌博主的分类占比
     * @param uid
     * @return
     */
    @Delete("delete from ins_st_blogger_fl_brand_category where uid = #{uid}")
    void deleteByUid(long uid);

    /**
     * 获取活跃粉丝品牌博主的分类占比
     * @param uid
     * @param limit
     * @return
     */
    @Select("SELECT t4.overall_category_name categoryName,1 brand_type, count(1)/(select count(1) from ( " +
            " SELECT baf.f_uid FROM ins_rel_blogger_active_follower baf " +
            " INNER JOIN ins_rel_blogger_following bf ON baf.f_uid = bf.uid  " +
            " WHERE baf.uid = #{uid} GROUP BY baf.f_uid )tc) categoryRate  " +
            " FROM ( SELECT t1.f_uid, t3.overall_category_name FROM " +
            " ( SELECT f_uid FROM ins_rel_blogger_active_follower WHERE uid = #{uid} ) t1 " +
            " INNER JOIN ins_rel_blogger_following t2 ON t1.f_uid = t2.uid " +
            " INNER JOIN ins_blogger t3 ON t2.f_uid = t3.uid  " +
            " WHERE t2.f_type = 1 AND t3.overall_category_name != ''  " +
            " GROUP BY t1.f_uid, t3.overall_category_name ) t4  " +
            " GROUP BY t4.overall_category_name ORDER BY categoryRate DESC LIMIT 0, #{limit} ")
    List<InsStBloggerFlBrandCategory> findActiveFollowerBrandCategoryRateByUid(long uid, int limit);

    /**
     * 获取活跃粉丝品牌博主的分类占比
     * @param uid
     * @param limit
     * @return
     */
    @Select("SELECT t4.overall_category_name categoryName,2 brand_type, count(1)/(select count(1) from ( " +
            " SELECT baf.f_uid FROM ins_rel_blogger_active_follower baf " +
            " INNER JOIN ins_rel_blogger_following bf ON baf.f_uid = bf.uid  " +
            " WHERE baf.uid = #{uid} GROUP BY baf.f_uid )tc) categoryRate  " +
            " FROM ( SELECT t1.f_uid, t3.overall_category_name FROM " +
            " ( SELECT f_uid FROM ins_rel_blogger_active_follower WHERE uid = #{uid} ) t1 " +
            " INNER JOIN ins_rel_blogger_following t2 ON t1.f_uid = t2.uid " +
            " INNER JOIN ins_blogger t3 ON t2.f_uid = t3.uid  " +
            " WHERE t3.overall_category_name != ''  " +
            " GROUP BY t1.f_uid, t3.overall_category_name ) t4  " +
            " GROUP BY t4.overall_category_name ORDER BY categoryRate DESC LIMIT 0, #{limit} ")
    List<InsStBloggerFlBrandCategory> findActiveFollowerCategoryRateByUid(long uid, int limit);
}